package io.file;

import com.alibaba.fastjson.JSONObject;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardOpenOption;
import java.util.HashMap;
import java.util.Objects;
import java.util.stream.Stream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.Test;

/**
 * @author zhang peipei
 */
public class ExcelIO {


    @Test
    public void writeFile() {
        try {
            String excelPath = "/Users/topjoy/Downloads/9-6-03-00.xlsx";
            InputStream inputStream = new FileInputStream(excelPath);
            Workbook workbook = new XSSFWorkbook(inputStream);

            String fileName = "/Users/topjoy/Documents/LBData.txt";
            Path filePath = Paths.get(fileName);
            // 使用newBufferedWriter创建文件并写文件
            // 这里使用了try-with-resources方法来关闭流，不用手动关闭
            // 追加写模式
            try (BufferedWriter writer = Files.newBufferedWriter(
                filePath,
                StandardCharsets.UTF_8,
                StandardOpenOption.CREATE)) {

                // 获取第2张单元表，第1张单元表使用不到
                Sheet sheet = workbook.getSheetAt(1);
                // sheet.getPhysicalNumberOfRows()获取总的行数
                int totalRows = sheet.getPhysicalNumberOfRows();
                System.out.println("total Num " + totalRows);
                // 循环读取每一行
                // 第1行不是数据名，数据从表格的第2行开始，所以起始从1开始
                for (int i = 1; i <= totalRows; i++) {
                    // 循环读取每一个格
                    Row row = sheet.getRow(i);

                    // row.getPhysicalNumberOfCells()获取总的列数
                    if (Objects.nonNull(row)) {
                        Cell rid = row.getCell(0);
                        Cell luoYangShove = row.getCell(1);
                        Cell digTreasure = row.getCell(2);
                        Cell shanHeSheJiTu = row.getCell(3);

                        String strRid = rid.getStringCellValue();

                        int luoYangShoveNum = (int)luoYangShove.getNumericCellValue();
                        int digTreasureNum = (int)digTreasure.getNumericCellValue();
                        int shanHeSheJiTuNum = (int)shanHeSheJiTu.getNumericCellValue();

//                        System.out.println("rid " + strRid);
//                        System.out.println("luoyang " + luoYangShoveNum);

                        writer.write(strRid);
                        writer.write(",");
                        writer.write(String.valueOf(luoYangShoveNum));
                        writer.write(",");
                        writer.write(String.valueOf(digTreasureNum));
                        writer.write(",");
                        writer.write(String.valueOf(shanHeSheJiTuNum));
                        writer.write('\n');
                    }
                }
            } catch (IOException ioException) {
                ioException.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Test
    private void check() {
        try {
            String excelPath = "/Users/topjoy/Downloads/9-6-03-00.xlsx";
            String txtPath = "/Users/topjoy/Documents/LBData.txt";
            InputStream inputStream = new FileInputStream(excelPath);
            Workbook workbook = new XSSFWorkbook(inputStream);

            Files.lines(Paths.get(txtPath)).count();

            // 获取第2张单元表，第1张单元表使用不到
            Sheet sheet = workbook.getSheetAt(1);
            // sheet.getPhysicalNumberOfRows()获取总的行数
            int totalRows = sheet.getPhysicalNumberOfRows();
            System.out.println("total Num " + totalRows);


            // 循环读取每一行
            // 第1行不是数据名，数据从表格的第2行开始，所以起始从1开始
            for (int i = 1; i <= totalRows; i++) {
                // 循环读取每一个格，row.getPhysicalNumberOfCells()获取总的列数
                Row row = sheet.getRow(i);
                if (Objects.nonNull(row)) {
                    Cell rid = row.getCell(0);
                    Cell luoYangShove = row.getCell(1);
                    Cell digTreasure = row.getCell(2);
                    Cell shanHeSheJiTu = row.getCell(3);

                    String strRid = rid.getStringCellValue();
                    int luoYangShoveNum = (int)luoYangShove.getNumericCellValue();
                    int digTreasureNum = (int)digTreasure.getNumericCellValue();
                    int shanHeSheJiTuNum = (int)shanHeSheJiTu.getNumericCellValue();

//                    var input = new FileReader("/Users/topjoy/Documents/LBData.txt");
                    Stream<String> lines = Files.lines(Paths.get(txtPath));
                    var lineI = lines.skip(i - 1).findFirst().get();

                    String[] array = lineI.split(",");
                    if (!array[0].equals(strRid) || Integer.parseInt(array[1]) != luoYangShoveNum
                        || Integer.parseInt(array[2]) != digTreasureNum || Integer.parseInt(array[3]) != shanHeSheJiTuNum) {

                        throw new RuntimeException("error line" + i);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void readTxt() {
        try {
            var input = new FileReader("/Users/topjoy/Documents/LBData.txt");

            BufferedReader br = new BufferedReader(input);

            //            System.out.println("total " + br.lines().count());

            // 这种流的方式不需要知道文件具体有多少行
            br.lines()
                .map(line -> line.split(","))
                .forEach(strArray -> {

                    String rid = strArray[0];
                    int luoYangShoveNum = Integer.parseInt(strArray[1]);
                    int digTreasureNum = Integer.parseInt(strArray[2]);
                    int shanHeSheJiTuNum = Integer.parseInt(strArray[3]);
                    System.out.println("rid " + rid + ", " + luoYangShoveNum + "," + digTreasureNum + "," + shanHeSheJiTuNum);

                    if (rid.equals("1000135_10073")) {
                        System.out.println(1 / luoYangShoveNum);
                    }
                });
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    private void writeJSON() {
        try {
            String path = "/Users/topjoy/Downloads/9-6-03-00.xlsx";

            InputStream inputStream = new FileInputStream(path);
            Workbook workbook = new XSSFWorkbook(inputStream);

            String fileName = "/Users/topjoy/Documents/LBData.json";
            Path filePath = Paths.get(fileName);
            // 使用newBufferedWriter创建文件并写文件
            // 这里使用了try-with-resources方法来关闭流，不用手动关闭
            // 追加写模式
            try (BufferedWriter writer = Files.newBufferedWriter(
                filePath,
                StandardCharsets.UTF_8,
                StandardOpenOption.CREATE)) {

                // 获取第2张单元表，第1张单元表使用不到
                Sheet sheet = workbook.getSheetAt(1);
                // sheet.getPhysicalNumberOfRows()获取总的行数
                // 循环读取每一行
                int totalRows = sheet.getPhysicalNumberOfRows();
                var map = new HashMap<String, String>();
                JSONObject jsonObject = new JSONObject();
                // 第1行不是数据名，数据从表格的第2行开始，所以起始从1开始
                for (int i = 1; i <= 20; i++) {
                    // 循环读取每一个格，如果已经知道要取哪一个格
                    Row row = sheet.getRow(i);

                    // row.getPhysicalNumberOfCells()获取总的列数
                    if (Objects.nonNull(row)) {
                        Cell rid = row.getCell(0);
                        Cell luoYangShove = row.getCell(1);
                        Cell digTreasure = row.getCell(2);
                        Cell shanHeSheJiTu = row.getCell(3);

                        String strRid = rid.getStringCellValue();

                        int luoYangShoveNum = (int)luoYangShove.getNumericCellValue();
                        int digTreasureNum = (int)digTreasure.getNumericCellValue();
                        int shanHeSheJiTuNum = (int)shanHeSheJiTu.getNumericCellValue();

                        System.out.println("rid " + strRid);
                        System.out.println("luoyang " + luoYangShoveNum);

                        map.put("rid", strRid);
                        map.put("luoYangShoveNum", String.valueOf(luoYangShoveNum));
                        map.put("digTreasureNum", String.valueOf(digTreasureNum));
                        map.put("shanHeSheJiTuNum", String.valueOf(shanHeSheJiTuNum));

                        jsonObject.putAll(map);

                        System.out.println(jsonObject);
//                        JSONObject jsonObject = JSONObject.parseObject(map.toString().replaceAll("=", ":"));

                        writer.write(jsonObject.toJSONString());

                        //                        writer.write(strRid);
//                        writer.write(",");
//                        writer.write(String.valueOf(luoYangShoveNum));
//                        writer.write(",");
//                        writer.write(String.valueOf(digTreasureNum));
//                        writer.write(",");
//                        writer.write(String.valueOf(shanHeSheJiTuNum));
//                        writer.write('\n');
                    }
                }
            } catch (IOException ioException) {
                ioException.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @Test
    public void readExcel() {
        try {
			/*// 如果需要通过URL获取资源的加上以下的代码，不需要的省略就行
			URL url = new URL(strURL);
			HttpURLConnection conn = (HttpURLConnection)url.openConnection();
			// 设置超时间为3秒
			conn.setConnectTimeout(3*1000);
			// 防止屏蔽程序抓取而返回403错误
			conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
			// 获取输入流
			InputStream inputStream = conn.getInputStream();
			......*/

            String path = "/Users/topjoy/Downloads/9-6-03-00.xlsx";
            //            String path = "/Users/topjoy/Downloads/test-96.xlsx";

            // 获取文件输入流
            InputStream inputStream = new FileInputStream(path);
            // 定义一个org.apache.poi.ss.usermodel.Workbook的变量
            Workbook workbook = null;
            // 截取路径名 . 后面的后缀名，判断是xls还是xlsx
            // 如果这个判断不对，就把equals换成 equalsIgnoreCase()
            if (path.substring(path.lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")){
                workbook = new XSSFWorkbook(inputStream);
            }else if (path.substring(path.lastIndexOf(" . " + 1)).equalsIgnoreCase("xls")){
                workbook = new HSSFWorkbook(inputStream);
            }

            // 获取第2张表，编号从0开始
            Sheet sheet = workbook.getSheetAt(1);
            // sheet.getPhysicalNumberOfRows()获取总的行数
            // 循环读取每一行
            int totalRows = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < 5; i++) {
                // 循环读取每一个格，如果已经知道要取哪一个格
                Row row = sheet.getRow(i);
                // row.getPhysicalNumberOfCells()获取总的列数
                if (Objects.nonNull(row)) {

                    Cell luoYangShove = row.getCell(1);
                    Cell digTreasure = row.getCell(2);
                    Cell shanHeSheJiTu = row.getCell(3);

                    System.out.println("cell: " + luoYangShove);
                    System.out.println("cell: " + digTreasure);
                    System.out.println("cell: " + shanHeSheJiTu);

//                    for (int index = 0; index < row.getPhysicalNumberOfCells(); index++) {
//                        // 获取数据，但是我们获取的cell类型
//                        //代码上的内容自己根据实际需要自己调整就可以，这里只是展示一个样式···~
//                        var cell = row.getCell(index);
//
//                        if (Objects.nonNull(cell)) {
//                            // 转换为字符串类型
//                            cell.setCellType(CellType.STRING);
//                            // 获取得到字符串
//                            String id = cell.getStringCellValue();
//
//                            System.out.println("cell: " + cell + ", id: " + id);
//                        }
//
//                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}

